


#oracle
#SELECT LEVEL  ,'name'||LEVEL name FROM DUAL CONNECT BY LEVEL <100
#SELECT ROWNUM  ,'name'||ROWNUM name FROM DUAL CONNECT BY ROWNUM <100




#WITH ... SELECT ...

WITH cte  AS
(
  SELECT 1 as col11, 2 col21
  UNION ALL
  SELECT 3, 4
)
SELECT col11, col21 FROM cte;





create table demo (
 id int not null,
 name varchar(50)
);

# INSERT ... WITH ... SELECT ..


insert into demo 
with t as (
 select 1 as  id, 'name1' 
 union all
 select 2 as  id, 'name2' 
)
select * from t




#WITH ... UPDATE ...
with t as (
 select 1 as  id, 'name1update'  as name
 union all
 select 2 as  id, 'name2update' 
)
update demo d,t set d.name=t.name where d.id=t.id

#WITH ... DELETE ..
with t as (
 select 1 as  id, 'name1update'  as name
 union all
 select 3 as  id, 'name2update' 
)
# DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
delete d  from demo  d ,t where d.id=t.id


select COUNT(*) from demo

truncate table demo;
# 批量插入数据
insert into demo

WITH RECURSIVE cte  AS
(
  SELECT 1 id,'namexx' as name
  UNION ALL
  SELECT id + 1,'namexx' FROM cte WHERE id < 10000000
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1000M) */  * FROM cte;

# 验证递归的层数

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte  #limit 1001
)
SELECT  /*+ SET_VAR(cte_max_recursion_depth = 1M) */  * FROM cte;




CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);


SELECT * FROM employees ORDER BY id;





WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;






drop table demotree;

CREATE TABLE demotree (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  pid        INT not null ,
  INDEX (pid)
);



INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (1, '01', 0);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (2, '02', 0);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (3, '0101', 1);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (4, '0102', 1);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (5, '010101', 3);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (6, '0201', 2);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (7, '01010101', 5);


select * from demotree


WITH RECURSIVE tree AS (
   #初始部分  id=1 的节点 
	SELECT t.* FROM demotree t WHERE t.id =1
	UNION ALL
	#递归部分  pid=初始部分.id
	SELECT t1.* FROM demotree t1,tree t WHERE t1.pid = t.id
)
select * from tree;



WITH RECURSIVE tree AS (
   #初始部分  id=7 的节点
	SELECT t.* FROM demotree t WHERE t.id=7
	UNION ALL
	#递归部分  id=初始部分.pid
	SELECT t1.* FROM demotree t1,tree t WHERE t1.id=t.pid
)
select * from tree;




WITH RECURSIVE tree AS ( 
	SELECT 1 lev ,t.* FROM demotree t WHERE t.id =1
	UNION ALL
	SELECT lev+1 lv, t1.* FROM demotree t1,tree t WHERE t1.pid = t.id and lev<= 3-1
)
select * from tree;



WITH RECURSIVE tree AS (
	SELECT 1 lev, t.* FROM demotree t WHERE t.id=7
	UNION ALL
	SELECT lev+1 lv,  t1.* FROM demotree t1,tree t WHERE t1.id=t.pid  and lev<= 3-1
)
select * from tree;



SELECT * FROM (VALUES ROW(2,4,8)) AS t(a,b,c)




WITH RECURSIVE t1  AS
(
  SELECT 1 n
  UNION ALL
  SELECT n + 1 FROM t1  limit 10
)
, t2 as(
  #SELECT * FROM (VALUES ROW(2,4,8)) AS t(a,b,c)

  select '男' n
  union all 
  select '女'
  union all 
  select '女'
)

SELECT  /*+ SET_VAR(cte_max_recursion_depth = 1M) */  t1.n,t2.n n2
FROM t1 cross join t2 ;



SELECT * FROM (VALUES ROW('男','女','女')) AS t(a,b,c)


